#
# Get product information for the passed product id ..when not passed function
# returns all the products 
#
get.products <- function(productId=NULL){ 
  
  strSQL <- 'SELECT productId, vendorId, productName, domainId FROM salesInsight.ProductMaster? ' 
  whereClause <- '' 
  if(!is.null(productId)) { 
    productIdStr <- str_c('\'',productId,'\'',collapse=',') whereClause <- str_c(' Where productId in (', productIdStr , ')') 
  } 
  
  dataFeed <- dbGetQuery(db.conn.qedb(),strc(strSQL, whereClause)) 
  return(dataFeed) 
} 

#
# Get product details by joining domain, algo, and vendor tables
#
get.productDetails <- function(productId=NULL){ 
  
  strSQL <- 'SELECT A.productId, A.vendorId, A.productName, A.domainId , C.vendorName, D.description, 
  B.searchAlgo, B.lastProcessDate 
  
  
  FROM  salesInsight.ProductMaster A, 
        salesInsight.KeywordController B, 
        salesInsight.VendorMaster C, 
        salesInsight.DomainMaster D 
  
  where A.productId = B.productId AND A.vendorId = C.vendorId AND A.domainId = D.domainId ' 
  
  whereClause <- '' 
  if(!is.null(productId)) { 
    productIdStr <- str_c('\'',productId,'\'',collapse=',') 
    whereClause <- str_c(' AND productId in (', productIdStr , ')') 
  } 
 
  dataFeed <- dbGetQuery(db.conn.qedb(),strc(strSQL, whereClause)) 
  return(dataFeed) 
} 

#
# Get all algos for a product
#
get.products.algos <- function(productId){ 
  
  strSQL <- 'SELECT productId, searchAlgo, productName, LastProcessDate FROM salesInsight.KeywordController ' 
  whereClause <- str_c('Where productId = ', productId) 
  
  dataFeed <- dbGetQuery(db.conn.qedb(),strc(strSQL, whereClause)) 
  
  return(dataFeed) 
} 

#
# Get company information by company id or company name
#
get.companies <- function(companyId=NULL, companyName=NULL){ 
  
  strSQL <- 'SELECT InsightCompId, companyName, city, StateCode, ZipCode FROM salesInsight.CompanyMaster ' 
  
  whereClause <- '' 
  if(!is.null(companyId)) { 
    whereClause <- str_c(' Where InsightCompId = ', companyId) 
  } 
  
  if(!is.null(companyName)) { 
    if(length(whereClause) > 0) { 
      whereClause <- str_c(whereClause, ' and companyName = \'', companyName, '\'') 
    } else { 
      whereClause <- str_c(' Where companyName = \'', companyName, '\'') 
    } 
  } 
  
  dataFeed <- dbGetQuery(db.conn.qedb(),strc(strSQL, whereClause)) 
  
  return(dataFeed) 
} 

#
# Checks if thejob  id already exists in the data base for the given source id
#
is.jobId.processed <- <- function(jobId, sourceId){ 
  
  returnData <- TRUE strSQL <- 'SELECT  FROM salesInsight.CompanyProductMap ' 
  whereClause <- str_c('Where JobId? = \'', jobId, '\'', ' AND sourceId = ', sourceId) 
  
  dataFeed <- dbGetQuery(db.conn.qedb(),strc(strSQL, whereClause)) 
  
  if(nrow(x=dataFeed) > 0 ) { 
    returnData <- FALSE 
  } return(returnData) 

} 

#
# get the last process date for a given algo
#
get.lastProcessesDate <- function(productId){ 
  
  strSQL <- 'SELECT LastProcessDate FROM salesInsight.KeywordController ' 
  whereClause <- str_c('Where productId = ', productId) 
  
  dataFeed <- dbGetQuery(db.conn.qedb(),strc(strSQL, whereClause)) 
  
  return(dataFeed) 
} 

write.companyProductMap <- function(companyProductMap) { 
  
  for (k in 1:nrow(companyProductMap)){ 
    insertSQL <- str_c('INSERT INTO salesInsight.CompanyProductMap VALUES (', 
                       companyProductMap$MapCode, 
                       companyProductMap$InsightCompanyId, 
                       companyProductMap$ProductId, 
                       companyProductMap$SourceId, 
                       companyProductMap$JobId, 
                       companyProductMap$RelevanceCode, 
                       companyProductMap$SearchSetId, ')' 
                      ) 
    code <- dbSendUpdate(db.conn.qedb(),insertSQL) 
  }
} 

write.searchSet <- function(searchSet) { 
  for (k in 1:nrow(searchSet)){ 
    insertSQL <- str_c('INSERT INTO salesInsight.SearchSet? VALUES (', 
                       searchSet$SearchSet, 
                       searchSet$Date, 
                       searchSet$ResultCount, 
                       searchSet$SearchSetDate, ')' 
                  ) 
    code <- dbSendUpdate(db.conn.qedb(),insertSQL) 
  }
} 